package com.zygh.lz.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zygh.lz.entity.SchedulingInformation;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;
import java.util.Map;

@Mapper
public interface SchedulingInfoMapper extends BaseMapper<SchedulingInformation> {

    /**
     * 查询当天清障车排班信息集合
     * @return
     * @param timeBucket
     */
    @Select("<script>" +
            "select * from scheduling_information " +
            "<where> " +
            " <if test='timeBucket != null '> " +
            " time_bucket = #{timeBucket} " +
            " </if> " +
            " and gmt_create like CONCAT(CURDATE(),'%') "+  //线上的时间有延迟
            " </where> " +
            " order by time_bucket "+  //线上的时间有延迟
            "</script>")
    @Results(id="result", value = {
            @Result(column = "id", property = "id", jdbcType = JdbcType.INTEGER),
            @Result(column = "point_name", property = "point_name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driver", property = "driver", jdbcType = JdbcType.VARCHAR),
            @Result(column = "driver_phone", property = "driver_phone", jdbcType = JdbcType.VARCHAR),
            @Result(column = "company_name", property = "company_name", jdbcType = JdbcType.VARCHAR),
            @Result(column = "number", property = "number", jdbcType = JdbcType.VARCHAR),
            @Result(column = "color", property = "color", jdbcType = JdbcType.VARCHAR),
            @Result(column = "latitude", property = "latitude", jdbcType = JdbcType.VARCHAR),
            @Result(column = "longitude", property = "longitude", jdbcType = JdbcType.VARCHAR),
            @Result(column = "start_at", property = "start_at", jdbcType = JdbcType.VARCHAR),
            @Result(column = "end_at", property = "end_at", jdbcType = JdbcType.VARCHAR),
            @Result(column = "time_bucket", property = "timeBucket", jdbcType = JdbcType.INTEGER),
            @Result(column = "gmt_create", property = "gmt_create", jdbcType = JdbcType.VARCHAR)
    })
    List<SchedulingInformation> selectAll(Integer timeBucket);

    /**
     * 根据时段和部门名称查询
     * @param timeBucket 时段
     * @param companyName 部门名称
     * @return
     */
    @Select("<script>" +
            "select COUNT(`point_name`) as counts, id,point_name,driver,driver_phone,company_name,`number`,color,latitude,longitude,start_at,end_at,time_bucket,gmt_create from scheduling_information " +
            "<where> " +
            " <if test='timeBucket != null '> " +
            " time_bucket = #{timeBucket} " +
            " </if> " +
            " <if test='companyName != null '> " +
            " and company_name = #{companyName} " +
            " </if> " +
            " and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 11 "+
            " </where> group by id" +
            "</script>")
    @ResultMap(value = "result")
    List<SchedulingInformation> getByTimeSection(@Param("timeBucket") Integer timeBucket,
                                                 @Param("companyName") String companyName);

    /**
     * 根据待命点和时段查询清障车详情
     * @param pointName 待命点
     * @param timeBucket 时段
     */
    @Select("<script>" +
            "select id,point_name,driver,driver_phone,company_name,`number`,color,latitude,longitude,start_at,end_at,time_bucket,gmt_create from scheduling_information  "+
            "<where> " +
            " <if test='timeBucket != null '> " +
            " time_bucket = #{timeBucket} " +
            " </if> " +
            //" and `point_name`=#{pointName} and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 15  "+
//            " and `point_name`=#{pointName} and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 11  "+
            " and `point_name`=#{pointName} and gmt_create like CONCAT(CURDATE(),'%') "+
            " </where> group by id" +
            "</script>")
    @ResultMap(value = "result")
    List<SchedulingInformation> getDetails(@Param("pointName") String pointName,
                                           @Param("timeBucket") Integer timeBucket);

    /**
     *每天早上五点删除七天前的清障车排班数据
     */
    @Delete("delete from scheduling_information where gmt_create < #{startYesterday}")
    void deleteSchedulingInfo(@Param("startYesterday") String startYesterday);

    /**
     * 获取平峰期的排班信息：平峰期+晚高峰的
     * @param timeBucket
     * @return
     */
    @Select("<script>" +
            "select * from scheduling_information " +
            "<where> " +
            " <if test='timeBucket != null '> " +
            " time_bucket != #{timeBucket} " +
            " </if> " +
            //" and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 15 order by time_bucket "+
//            " and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 11 order by time_bucket "+
            " and gmt_create like CONCAT(CURDATE(),'%')"+
            " </where> " +
            "order by time_bucket "+
            "</script>")
    @ResultMap(value = "result")
    List<SchedulingInformation> getCenterInfo(Integer timeBucket);

    @Select("select * from scheduling_information " +
            " where id = #{id} " +
            " and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) <= 150 limit 1 ")
    @ResultMap(value = "result")
    SchedulingInformation getByIdAndNumber(Integer id);

    @Select("<script>" +
            "select id,driver,number from scheduling_information " +
            "<where> " +
            " <if test='number != null and number.length &gt;= 0'> " +
            " AND (`number` LIKE CONCAT('%',#{number},'%') OR driver LIKE" +
            " CONCAT('%',#{number},'%'))"+
            " </if> " +
            //" and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 15 group by driver "+
            " and gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) &lt;= 11 group by driver "+
            " </where> " +
            "</script>")
    List<Map<String, Object>> findByNumber(String driver);

    @Select("SELECT COUNT(*) as waitPointCount from (SELECT * FROM `scheduling_information` WHERE " +
            //" gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) <= 15 GROUP BY point_name ) a")
//            " gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) <= 11 GROUP BY point_name ) a")
            " gmt_create like CONCAT(CURDATE(),'%') GROUP BY point_name ) a")
    Integer getWaitPointCount();

    @Select("SELECT COUNT(*) as carCount from (SELECT * FROM `scheduling_information` WHERE " +
            " gmt_create like CONCAT(CURDATE(),'%') and TIMESTAMPDIFF(MINUTE,gmt_create,NOW()) <= 11 GROUP BY number ) a")
    Integer getSchedulingCarCount();
}
